//
//  LHCSqlOperate.m
//  MyAddressBook
//
//  Created by lihongcheng on 16/4/22.
//  Copyright © 2016年 lihongcheng. All rights reserved.
//

#import "LHCSqlOperate.h"

@implementation LHCSqlOperate

+(BOOL)insertDataIntoSql:(LHCDataModel *)model
{
    NSString *dbString = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/User.db"];
    FMDatabase *database = [[FMDatabase alloc] initWithPath:dbString];
    [database open];
    //创建表格的SQL语句//primary key不能重复//autoincrement自增长
    NSString *createTableSql = @"create table if not exists MyFriends(id integer primary key autoincrement, name varchar(256), phone varchar(256), age varchar(256), email varchar(256),address varchar(256))";
    //创建数据困中得表格
    BOOL table = [database executeUpdate:createTableSql];
    NSLog(@"table = %i",table);
    NSLog(@"path = %@",dbString);
    
    //创建插入数据的SQL语句
    NSString *insertSql = @"insert into MyFriends(name,phone,age,email,address) values (?,?,?,?,?)";
    BOOL insert = [database executeUpdate:insertSql,model.name,model.phone,model.age,model.email,model.address];
    NSLog(@"insert = %i",insert);
    [database close];
    
    return insert;
}

+(NSMutableArray *)readDataFromSql
{
    NSMutableArray *dataArray = [[NSMutableArray alloc] init];
    
    NSString *dbString = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/User.db"];
    FMDatabase *database = [[FMDatabase alloc] initWithPath:dbString];
    [database open];
    
    NSString *selectString = @"select * from MyFriends";
    FMResultSet *set = [database executeQuery:selectString];
    while ([set next]) {
        LHCDataModel *model = [[LHCDataModel alloc] init];
        model.name  = [set stringForColumn:@"name"];
        model.phone  = [set stringForColumn:@"phone"];
        model.age  = [set stringForColumn:@"age"];
        model.email  = [set stringForColumn:@"email"];
        model.address  = [set stringForColumn:@"address"];
        model.rowId = [[set stringForColumn:@"id"] integerValue];
        [dataArray addObject:model];
    }
    [database close];
    return dataArray;
}
+(BOOL)updateDataFromSql:(LHCDataModel *)model andbefore:(LHCDataModel *)beModel
{
    NSString *dbString = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/User.db"];
    FMDatabase *database = [[FMDatabase alloc] initWithPath:dbString];
    [database open];
    //判断条件可用id，如下面的删除数据
    NSString *updateString = @"update MyFriends set name = ?,  phone = ? , age = ? , email = ? , address = ? where name = ? and phone = ?";
    BOOL update = [database executeUpdate:updateString,model.name,model.phone,model.age,model.email,model.address,beModel.name,beModel.phone];
    
    NSLog(@"update = %i",update);
     NSLog(@"path = %@",dbString);
    
    [database close];
    return update;
    
}
+(BOOL)deleteDataFromSql:(LHCDataModel *)model
{
    NSString *dbString = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/User.db"];
    FMDatabase *database = [[FMDatabase alloc] initWithPath:dbString];
    [database open];
    NSString *deleteString = @"delete from MyFriends where id = ?";
    BOOL delete = [database executeUpdate:deleteString,[NSString stringWithFormat:@"%ld",model.rowId]];
    NSLog(@"delete = %i",delete);
    return delete;
}

@end
